

-- ================================================================      

-- Author:   <May Myat Thu>      

-- Role:   <Programmer>      

-- Create date:  <13.10.2014>      

-- Description:  <sp_cr_DefinePaymentTypeRpt>      

-- Procedure Name: <[dbo].[sp_cr_DefinePaymentTypeRpt]>      

-- Return Type:  <Table>      

-- ================================================================      

-- UPDATED HISTORY      

-- ================================================================      

-- Name:     

-- Date:     

-- Description:       

      

-- Name:     

-- Date:   

-- Description:      

-- Name:      

-- Date: 

-- Description:     

-- ================================================================  





CREATE procedure [dbo].[sp_cr_DefinePaymentTypeRpt]



	@fromdate as varchar(30),

	@todate as varchar(30),

	@frominvoiceid as nvarchar(4000),

	@toinvoiceid as nvarchar(4000),

	@frombranchid as nvarchar(4000),

	@tobranchid as nvarchar(4000),

	@fromagentid as nvarchar(4000),

	@toagentid as nvarchar(4000),

	@fromtypeid as nvarchar(4000),

	@totypeid as nvarchar(4000),

	@fromreceiptno as nvarchar(4000),

	@toreceiptno as nvarchar(4000),

	@fromuserid as nvarchar(4000),

	@touserid as nvarchar(4000),

	@fromcurrencyid as nvarchar(4000),

	@tocurrencyid as nvarchar(4000),

	

	@extrastatus VARCHAR(20),

	@rptstatus VARCHAR(20),

	@orderby VARCHAR(20),



    @branch_id as varchar(200)



AS 

BEGIN

	DECLARE @groupby VARCHAR(200)

	DECLARE @sTitle VARCHAR(100)

	DECLARE @Title VARCHAR(100)

	DECLARE @Temp VARCHAR(100)

	DECLARE @rowno VARCHAR(200)

	DECLARE @field VARCHAR(4000)

	DECLARE @extrafield VARCHAR(4000)



	DECLARE @colinvoiceid varchar(1000)

	DECLARE @colbranchid varchar(1000)

	DECLARE @colagentid varchar(1000)

	DECLARE @coltypeid varchar(1000)

	DECLARE @colreceiptno varchar(1000)

	DECLARE @coluserid varchar(1000)

	DECLARE @colcurrencyid varchar(1000)

	



	--invoiceid

	IF(@frominvoiceid <> '' AND @toinvoiceid <> '' AND @frominvoiceid > @toinvoiceid)

	BEGIN

		SET @Temp = @frominvoiceid

		SET @frominvoiceid = @toinvoiceid

		SET @toinvoiceid = @Temp

	END

	

	IF(@frominvoiceid = '' AND @toinvoiceid = '')

		SET @colinvoiceid = ''

		

	ELSE

	BEGIN

	IF(@frominvoiceid = '') SET @frominvoiceid = (SELECT TOP 1 InvoiceNo FROM dbo.DefinePaymentType ORDER BY InvoiceNo ASC)

	IF(@toinvoiceid = '') SET @toinvoiceid = (SELECT TOP 1 InvoiceNo FROM dbo.DefinePaymentType ORDER BY InvoiceNo DESC)

		SET @colinvoiceid = 'AND(dpt.InvoiceNo Between N'''+@frominvoiceid+''' AND N'''+@toinvoiceid+''')'

	END



	--branchid

	IF(@frombranchid <> '' AND @tobranchid <> '' AND @frombranchid > @tobranchid)

	BEGIN

		SET @Temp = @frombranchid

		SET @frombranchid = @tobranchid

		SET @tobranchid = @Temp

	END

	

	IF(@frombranchid = '' AND @tobranchid = '')

		SET @colbranchid = ''

		

	ELSE

	BEGIN

	IF(@frombranchid = '') SET @frombranchid = (SELECT TOP 1 BranchCode FROM dbo.Branch ORDER BY BranchCode ASC)

	IF(@tobranchid = '') SET @tobranchid = (SELECT TOP 1 BranchCode FROM dbo.Branch ORDER BY BranchCode DESC)

		SET @colbranchid = 'AND(BranchCode Between N'''+@frombranchid+''' AND N'''+@tobranchid+''')'

	END



	--agentID

	IF(@fromagentid <> '' AND @toagentid <> '' AND @fromagentid > @toagentid)

	BEGIN

		SET @Temp = @fromagentid

		SET @fromagentid = @toagentid

		SET @toagentid = @Temp

	END

	

	IF(@fromagentid = '' AND @toagentid = '')

		SET @colagentid = ''

		

	ELSE

	BEGIN

	IF(@fromagentid = '') SET @fromagentid = (SELECT TOP 1 agent_code FROM dbo.Agent ORDER BY agent_code ASC)

	IF(@toagentid = '') SET @toagentid = (SELECT TOP 1 agent_code FROM dbo.Agent ORDER BY agent_code DESC)

		SET @colagentid = 'AND(agent_code Between N'''+@fromagentid+''' AND N'''+@toagentid+''')'

	END



	--TypeID

	IF(@fromtypeid <> '' AND @totypeid <> '' AND @fromtypeid > @totypeid)

	BEGIN

	 SET @Temp= @fromtypeid

	 SET @fromtypeid= @totypeid

	 SET @totypeid= @Temp

	END



	IF (@fromtypeid ='' and @totypeid ='')

	   SET @coltypeid =''

	ELSE 

	  BEGIN

		  IF @fromtypeid='' SET @fromtypeid= (SELECT TOP 1 type_code FROM dbo.Type ORDER BY type_code ASC)

		  IF @totypeid='' SET @totypeid= ( SELECT TOP 1 type_code FROM dbo.Type ORDER BY type_code DESC)

		   SET @coltypeid = ' AND(dptd.TypeCode BETWEEN N'''+@fromtypeid+ ''' AND '''+@totypeid+''')'

	END



	--UserID

	IF(@fromuserid <> '' AND @touserid <> '' AND @fromuserid > @touserid)

	BEGIN

	 SET @Temp= @fromuserid

	 SET @fromuserid= @touserid

	 SET @touserid= @Temp

	END



	IF (@fromuserid ='' and @touserid ='')

	   SET @coluserid =''

	ELSE 

	  BEGIN

		  IF @fromuserid='' SET @fromuserid= (SELECT TOP 1 UserName FROM dbo.[User] ORDER BY UserName ASC)

		  IF @touserid='' SET @touserid= ( SELECT TOP 1 UserName FROM dbo.[User] ORDER BY UserName DESC)

		   SET @coluserid = ' AND(UserName BETWEEN N'''+@fromuserid+ ''' AND '''+@touserid+''')'

	END



	--Currency

	IF(@fromcurrencyid <> '' AND @tocurrencyid <> '' AND @fromcurrencyid > @tocurrencyid)

	BEGIN

		SET @Temp = @fromcurrencyid

		SET @fromcurrencyid = @tocurrencyid

		SET @tocurrencyid = @Temp

	END

	

	IF(@fromcurrencyid = '' AND @tocurrencyid = '')

		SET @colcurrencyid = ''

		

	ELSE

	BEGIN

	IF(@fromcurrencyid = '') SET @fromcurrencyid = (SELECT TOP 1 CurrencyCode FROM dbo.Currency ORDER BY CurrencyCode ASC)

	IF(@tocurrencyid = '') SET @tocurrencyid = (SELECT TOP 1 CurrencyCode FROM dbo.Currency ORDER BY CurrencyCode DESC)

		SET @colcurrencyid = 'AND(CurrencyCode Between N'''+@fromcurrencyid+''' AND N'''+@tocurrencyid+''')'

	END



	--ReceiptNo

	IF(@fromreceiptno <> '' AND @toreceiptno <> '' AND @fromreceiptno > @toreceiptno)

	BEGIN

	 SET @Temp= @fromreceiptno

	 SET @fromreceiptno= @toreceiptno

	 SET @toreceiptno= @Temp

	END



	IF (@fromreceiptno ='' and @toreceiptno ='')

	   SET @colreceiptno =''

	ELSE 

	  BEGIN

		  IF @fromreceiptno='' SET @fromreceiptno= (SELECT TOP 1 ReceiptNo FROM dbo.CashRemittanceByAgent ORDER BY ReceiptNo ASC)

		  IF @toreceiptno='' SET @toreceiptno= ( SELECT TOP 1 ReceiptNo FROM dbo.CashRemittanceByAgent ORDER BY ReceiptNo DESC)

		   SET @colreceiptno = ' AND(ReceiptNo BETWEEN N'''+@fromreceiptno+ ''' AND '''+@toreceiptno+''')'

	END



	IF(@orderby = 'date')

	BEGIN

		SET @groupby = 'CONVERT(VARCHAR(10),CAST(dpt.InvoiceDate AS DATE),103 )'

		SET @Title = 'Date'	

	END

	ELSE IF(@orderby = 'invoiceid')

	BEGIN

		SET @groupby = 'dpt.InvoiceNo'

		SET @Title = 'Invoice ID'	

	END

	ELSE IF(@orderby = 'branchid')

	BEGIN

		SET @groupby = 'dpt.BranchCode'

		SET @Title = 'Branch ID'	

	END

	ELSE IF(@orderby = 'agentid')

	BEGIN

		SET @groupby = 'agent_code'

		SET @Title = 'Agent ID'

	END	

	ELSE IF(@orderby = 'typeid')

	BEGIN

		SET @groupby = 'Code'

		SET @Title = 'Type ID'	

	END

	ELSE IF(@orderby = 'Userid')

	BEGIN

		SET @groupby = 'uu.UserName'

		SET @Title = 'User ID'

	END

	ELSE IF(@orderby = 'currencyid')

	BEGIN

		SET @groupby = 'CurrencyCode'

		SET @Title = 'Currency ID'	

	END

	ELSE IF(@orderby = 'receiptno')

	BEGIN

		SET @groupby = 'dptd.ReceiptNo'

		SET @Title = 'Receipt No'	

	END



	IF(@rptstatus = 'summary')

	BEGIN

		SET @field = @groupby+' AS SummaryGroup'

		SET @rowno = 'SummaryGroup'

		SET @Title = '''Cash Remittance Summary Information By '+@Title+''''

	END



	ELSE IF(@rptstatus = 'detail')

	BEGIN

		SET @field =	@groupby+' AS DetailGroup,

						convert(char(10),dpt.InvoiceDate,103)Date,

						substring(cast(dpt.InvoiceDate as nvarchar(30)),13,7)Time ,

						dptd.TypeCode, 

						dpt.BranchCode'

		SET @rowno = 'DetailGroup'

		SET @Title = '''Cash Remittance Details Information By '+@Title+' '''

	END



	IF(@extrastatus = 'paid')

	BEGIN

		SET @extrafield = 'and a.ExtraFees is not null'

	END



	ELSE IF(@extrastatus = 'notpaid')

	BEGIN

		SET @extrafield = 'and a.ExtraFees is null'

	END



	ELSE IF(@extrastatus = 'paidornot')

	BEGIN

		SET @extrafield = ''

	END

	

	DECLARE @sql VARCHAR(8000)



	set @sql = 'select * from (select distinct '+@groupby+' as GroupName, '+@Title+' AS title, ROW_NUMBER ()over (order by c.CurrencyCode,'+@groupby+')No, 

				dptd.AgentCode,a.name,dptd.ReceiptNo,dptd.Sender,dptd.Amount,dptd.Receiver,dptd.Address,c.CurrencyCode,dptd.ExtraFees, 

				(case when dptd.ModifiedBy is null then uu.UserName else u.UserName end)username,

				'+@field+'

				from  DefinePaymentType dpt

				inner join DefinePaymentTypeDetail dptd on dptd.InvoiceID = dpt.InvoiceID

				left join Agent a on a.agent_id = dptd.AgentID

				inner join Currency c on c.CurrencyId = dptd.CurrencyID

				left join [User] u on u.UserId = dptd.ModifiedBy

				left join [User] uu on uu.UserId = dptd.CreatedBy

				where dpt.BranchID = '+@branch_id+'

				and (InvoiceDate BETWEEN '''+@fromdate+''' AND '''+@todate+''') ' + @colreceiptno + @colagentid + @coltypeid + @colcurrencyid + @colinvoiceid + @colbranchid + ') a where 1 = 1 '

				

				--+@colreceiptno

				--+@colagentid

				--+@coltypeid

				+@coluserid

				--+@colcurrencyid

				--+@colinvoiceid

				--+@colbranchid

				+@extrafield

				--+')a'

	 EXEC (@sql)

	 SELECT (@sql)

 END
GO


